/////// This code produces tables and figures in Jiang, Matvos, Piskorski, and Seru (2025)  //////

global raw "Replication_Package\Data\raw\"
global processed "Replication_Package\Data\processed\"
global output "Replication_Package\Output\"

//////////////////////////////////////////////////////////////////////////////
////////////////// Table 1: summary statistics  ///////////////////////////////
//////////////////////////////////////////////////////////////////////////////
use "${processed}analysis_loss_2022q1_2024q1",clear
gen cre_loan = reloan-reloan_residential1to4 

egen agg_at  = sum(total_asset)
egen as_at = sum(total_asset), by(size_bin)
egen agg_cre = sum(cre_loan)
egen as_cre = sum(cre_loan), by(size_bin)

replace agg_at = agg_at/1000000000 // trillion 
replace as_at = as_at/1000000 // billion 
replace agg_cre = agg_cre/1000000 // billion 
replace as_cre = as_cre/1000000 // billion
gen cre_loan_ratio = 100*cre_loan/total_asset
eststo clear
eststo tot_a:  estpost summarize agg_at agg_cre cre_loan_ratio,detail
eststo small_a: quietly estpost summarize  as_at as_cre cre_loan_ratio if size_bin=="small" & quarter==1,detail
eststo large_a: quietly estpost summarize as_at as_cre cre_loan_ratio  if size_bin=="large" & quarter==1,detail
eststo gsib_a: quietly estpost summarize as_at as_cre cre_loan_ratio  if size_bin=="gsib" & quarter==1,detail
esttab using "${output}table_1.rtf", cells(`"mean(fmt(1))"' `"sd(fmt(1))"' `"p5(fmt(1))"' `"p25(fmt(1))"' `"p50(fmt(1))"'  `"p75(fmt(1))"' `"p95(fmt(1))"') label replace 

//////////////////////////////////////////////////////////////////////////////
/////////////// Table 2: Distress risk of CRE Loans  ///////////////////////////////
//////////////////////////////////////////////////////////////////////////////
/// this table uses licensed DBRS and Green Street Advisor data
// we provide pseudo data in the replication package to illustrate the program 
///////////////////////////////// output //////////////////////////////////////
use "${processed}cre_loan_pseudo",replace 
gen market_for_output = "Office" if property_type=="Office"
replace market_for_output = "Multifamily" if property_type=="Apartment"
replace market_for_output = "Industrial" if property_type=="Industrial"
replace market_for_output = "Retail" if property_type=="Strip Center"
replace market_for_output = "Hotels" if strpos(propertytype, "Hotel")>0
replace market_for_output = "Other" if missing(market_for_output)

egen total_current_balance = sum(currenttrustbalance), by(market_for_output)
egen agg_balance = sum(currenttrustbalance)
gen balance_share = total_current_balance/agg_balance 


gen underwater_80 = (current_ltv>0.8)
gen underwater = (current_ltv>1)
gen current_dscr_less1 = (precedingdscr<1)
gen current_dscr_less1p2 = (precedingdscr<1.2)

gen refi_dscr_less1 = (dscr_refi<1)
gen refi_dscr_less1p2 = (dscr_refi<1.2)

gen under_current_dscr_less1 = 1 if underwater==1 & current_dscr_less1==1 
replace under_current_dscr_less1 = 0 if missing(under_current_dscr_less1) 
gen under_refi_dscr_less1 = 1 if underwater==1 & refi_dscr_less1==1 
replace under_refi_dscr_less1 = 0 if missing(under_refi_dscr_less1) 

gen under_current_dscr_less1p2 = 1 if underwater_80==1 & current_dscr_less1p2==1 
replace under_current_dscr_less1p2 = 0 if missing(under_current_dscr_less1p2) 
gen under_refi_dscr_less1p2 = 1 if underwater_80==1 & refi_dscr_less1p2==1 
replace under_refi_dscr_less1p2 = 0 if missing(under_refi_dscr_less1p2) 


gen under_or_current_dscr_less1p2 = 1 if underwater_80==1 | current_dscr_less1p2==1 
replace under_or_current_dscr_less1p2 = 0 if missing(under_current_dscr_less1p2) 

gen under_or_refi_dscr_less1p2 = 1 if underwater_80==1 | refi_dscr_less1p2==1 
replace under_or_refi_dscr_less1p2 = 0 if missing(under_refi_dscr_less1p2) 


// keep missing as missing 
replace underwater = . if missing(current_ltv)
replace underwater_80 = . if missing(current_ltv)
replace current_dscr_less1 = . if missing(precedingdscr)
replace current_dscr_less1p2 = . if missing(precedingdscr)
replace refi_dscr_less1 = . if missing(dscr_refi)
replace refi_dscr_less1p2 = . if missing(dscr_refi)
replace under_current_dscr_less1 = . if missing(current_ltv) | missing(precedingdscr)
replace under_current_dscr_less1p2 = . if missing(current_ltv) | missing(precedingdscr)
replace under_refi_dscr_less1=. if missing(current_ltv) | missing(dscr_refi)
replace under_refi_dscr_less1p2=. if missing(current_ltv) | missing(dscr_refi)
replace under_or_current_dscr_less1p2 = . if missing(current_ltv) | missing(precedingdscr)
replace under_or_refi_dscr_less1p2=. if missing(current_ltv) | missing(dscr_refi)


foreach x of var dscr_refi{
_pctile `x', nq(100)
replace `x'=r(r99) if `x'>r(r99) & !missing(`x')
}

// value weighted - full sample 
gen w = currenttrustbalance/agg_balance
foreach x of var  ltviss current_ltv underwater_80 underwater interestrate refi_rate issuerdscr precedingdscr dscr_refi current_dscr_less1  refi_dscr_less1 under_current_dscr_less1 under_refi_dscr_less1 current_dscr_less1p2 refi_dscr_less1p2 under_current_dscr_less1p2 under_refi_dscr_less1p2 under_or_current_dscr_less1p2 under_or_refi_dscr_less1p2 {
gen `x'_w = `x'*w 
egen a_`x' = sum(`x'_w)
}

drop w *_w
gen w = currenttrustbalance/total_current_balance
foreach x of var  ltviss  current_ltv underwater_80 underwater interestrate refi_rate issuerdscr precedingdscr dscr_refi current_dscr_less1  refi_dscr_less1 under_current_dscr_less1 under_refi_dscr_less1 current_dscr_less1p2 refi_dscr_less1p2 under_current_dscr_less1p2 under_refi_dscr_less1p2 under_or_current_dscr_less1p2 under_or_refi_dscr_less1p2{
gen `x'_w = `x'*w 
egen s_`x' = sum(`x'_w), by(market_for_output)
}

/// Panel A
eststo clear 
eststo: estpost sum currenttrustbalance a_ltviss a_current_ltv a_underwater_80 a_underwater
eststo: estpost sum balance_share currenttrustbalance s_ltviss s_current_ltv s_underwater_80 s_underwater  if market_for_output=="Office"
esttab using "${output}table_2A.rtf", cells("mean(fmt(3))") label replace 


// Panel C
eststo clear 
eststo: estpost sum a_issuerdscr a_precedingdscr a_dscr_refi a_current_dscr_less1 a_refi_dscr_less1 a_refi_dscr_less1p2  a_under_or_refi_dscr_less1p2 a_under_refi_dscr_less1
eststo: estpost sum s_issuerdscr s_precedingdscr s_dscr_refi s_current_dscr_less1 s_refi_dscr_less1 s_refi_dscr_less1p2  s_under_or_refi_dscr_less1p2 s_under_refi_dscr_less1  if market_for_output=="Office"
esttab using "${output}table_2C.rtf", cells("mean(fmt(3))") label replace 


//////////////////////////////////////////////////////////////////////////////
/////////////// Figures 1, 3, 5///////////////////////////////
//////////////////////////////////////////////////////////////////////////////

use "${processed}analysis_loss_2022q1_2024q1",clear
gen cre_loan = reloan-reloan_residential1to4 

forvalue x = 0(2)20{
gen cre_loss = 0.3*cre_loan*`x'/100 // 70% recovery rate 

// with only cre default or extreme case 
gen dd_equity = total_equity-cre_loss 
gen mm_dd_equity = total_equity-cre_loss-loss

gen neg_dd_equity = (dd_equity<0)
gen neg_mm_equity = (mm_dd_equity<0)

egen agg_default_loss_`x' = sum(cre_loss)
egen count_neg_extreme_dd_`x' = sum(neg_dd_equity)
egen count_neg_extreme_mm_`x' = sum(neg_mm_equity)
egen agg_asset_extreme_neg_dd_`x' = sum(neg_dd_equity*total_asset)
egen agg_asset_extreme_neg_mm_`x' = sum(neg_mm_equity*total_asset)
egen agg_equity_extreme_deficit_dd_`x' = sum(neg_dd_equity*dd_equity)
egen agg_equity_extreme_deficit_mm_`x' = sum(neg_mm_equity*mm_dd_equity)

drop  dd_equity mm_dd_equity neg_dd_equity neg_mm_equity

// with 50% insured depositor run 
gen dd_insured_coverage = total_asset-cre_loss-0.5*uninsured_deposit-insured_deposit 
gen mm_dd_insured_coverage = total_asset-cre_loss-loss-0.5*uninsured_deposit-insured_deposit 

gen neg_dd_insured_coverage = (dd_insured_coverage<0)
gen neg_mm_insured_coverage = (mm_dd_insured_coverage<0)

egen count_neg_50run_dd_`x' = sum(neg_dd_insured_coverage)
egen count_neg_50run_mm_`x' = sum(neg_mm_insured_coverage)
egen agg_asset_50run_neg_dd_`x' = sum(neg_dd_insured_coverage*total_asset)
egen agg_asset_50run_neg_mm_`x' = sum(neg_mm_insured_coverage*total_asset)
egen agg_insure_50run_deficit_dd_`x' = sum(neg_dd_insured_coverage*dd_insured_coverage)
egen agg_insure_50run_deficit_mm_`x' = sum(neg_mm_insured_coverage*mm_dd_insured_coverage)

drop  dd_insured_coverage mm_dd_insured_coverage neg_dd_insured_coverage neg_mm_insured_coverage

// with 100% insured depositor run 
gen dd_insured_coverage = total_asset-cre_loss-uninsured_deposit-insured_deposit 
gen mm_dd_insured_coverage = total_asset-cre_loss-loss-uninsured_deposit-insured_deposit 

gen neg_dd_insured_coverage = (dd_insured_coverage<0)
gen neg_mm_insured_coverage = (mm_dd_insured_coverage<0)

egen count_neg_100run_dd_`x' = sum(neg_dd_insured_coverage)
egen count_neg_100run_mm_`x' = sum(neg_mm_insured_coverage)
egen agg_asset_100run_neg_dd_`x' = sum(neg_dd_insured_coverage*total_asset)
egen agg_asset_100run_neg_mm_`x' = sum(neg_mm_insured_coverage*total_asset)
egen agg_insure_100run_deficit_dd_`x' = sum(neg_dd_insured_coverage*dd_insured_coverage)
egen agg_insure_100run_deficit_mm_`x' = sum(neg_mm_insured_coverage*mm_dd_insured_coverage)

drop cre_loss dd_insured_coverage mm_dd_insured_coverage neg_dd_insured_coverage neg_mm_insured_coverage


}



keep  count_neg_* agg_asset_* agg_equity_* agg_insure_*  agg_default_loss_*
duplicates drop

gen id = 1 
reshape long agg_default_loss_ count_neg_extreme_dd_ count_neg_extreme_mm_ agg_asset_extreme_neg_dd_ agg_asset_extreme_neg_mm_ agg_equity_extreme_deficit_dd_ agg_equity_extreme_deficit_mm_ count_neg_50run_dd_ count_neg_50run_mm_ agg_asset_50run_neg_dd_ agg_asset_50run_neg_mm_ agg_insure_50run_deficit_dd_ agg_insure_50run_deficit_mm_ count_neg_100run_dd_ count_neg_100run_mm_ agg_asset_100run_neg_dd_ agg_asset_100run_neg_mm_ agg_insure_100run_deficit_dd_ agg_insure_100run_deficit_mm_, i(id) j(default)

foreach x of var agg_default_loss_ agg_equity* agg_insure_* {
replace `x' = `x'/1000000 // billion
}

foreach x of var agg_asset_* {
replace `x' = `x'/1000000000 // trillion
}


// Figure 1
graph bar agg_default_loss_  if default>0, over(default)  ///
bar(1,color(black)) ylabel(,nogrid noticks) plotregion(lcolor(black)) graphregion(fcolor(white) color(white) lcolor(white)) ytitle("CRE Default Loss (Billion)") b1title("Percentage of CRE Default (%)")
graph export "${output}figure_1.eps", as(eps) replace 


// Figure 3 extreme insolvency 
sort id default 
by id:gen extra_n_neg_extreme = count_neg_extreme_mm_-count_neg_extreme_mm_[1]
by id: gen extra_asset_neg_extreme = agg_asset_extreme_neg_mm_-agg_asset_extreme_neg_mm_[1]
by id: gen extra_equity_neg_extreme = -(agg_equity_extreme_deficit_mm_-agg_equity_extreme_deficit_mm_[1])


graph bar extra_n_neg_extreme if default>0  , over(default)  ///
bar(1,color(gs2)) ylabel(,nogrid noticks) plotregion(lcolor(black)) graphregion(fcolor(white) color(white) lcolor(white)) ytitle("Number of Banks") b1title("Percentage of CRE Default (%)")
graph export "${output}figure_3A.eps", as(eps) replace 

graph bar extra_asset_neg_extreme if default>0  , over(default)  ///
bar(1,color(gs2)) ylabel(,nogrid noticks) plotregion(lcolor(black)) graphregion(fcolor(white) color(white) lcolor(white)) ytitle("Total Asset (Trillion)") b1title("Percentage of CRE Default (%)")
graph export "${output}figure_3B.eps", as(eps) replace 

graph bar extra_equity_neg_extreme if default>0  , over(default)  ///
bar(1,color(gs2)) ylabel(,nogrid noticks) plotregion(lcolor(black)) graphregion(fcolor(white) color(white) lcolor(white)) ytitle("Equity Deficit (Billion)") b1title("Percentage of CRE Default (%)")
graph export "${output}figure_3C.eps", as(eps) replace 


// Figure 5: 50% run  

sort id default 
by id:gen extra_n_neg_50 = count_neg_50run_mm_-count_neg_50run_mm_[1]
by id: gen extra_asset_neg_50 = (agg_asset_50run_neg_mm_-agg_asset_50run_neg_mm_[1])*1000 // billion


graph bar extra_n_neg_50 if default>0  , over(default)  ///
bar(1,color(gs2)) ylabel(,nogrid noticks) plotregion(lcolor(black)) graphregion(fcolor(white) color(white) lcolor(white)) ytitle("Number of Banks") b1title("Percentage of CRE Default (%)")
graph export "${output}figure_5A.eps", as(eps) replace 

graph bar extra_asset_neg_50 if default>0  , over(default)  ///
bar(1,color(gs2)) ylabel(,nogrid noticks) plotregion(lcolor(black)) graphregion(fcolor(white) color(white) lcolor(white)) ytitle("Total Asset (Billion)") b1title("Percentage of CRE Default (%)")
graph export "${output}figure_5B.eps", as(eps) replace 

// Figure 5: 100% run 
sort id default 
by id:gen extra_n_neg_100 = count_neg_100run_mm_-count_neg_100run_mm_[1]
by id: gen extra_asset_neg_100 = (agg_asset_100run_neg_mm_-agg_asset_100run_neg_mm_[1])*1000 // billion


graph bar extra_n_neg_100 if default>0  , over(default)  ///
bar(1,color(gs2)) ylabel(,nogrid noticks) plotregion(lcolor(black)) graphregion(fcolor(white) color(white) lcolor(white)) ytitle("Number of Banks") b1title("Percentage of CRE Default (%)")
graph export "${output}figure_5C.eps", as(eps) replace 

graph bar extra_asset_neg_100 if default>0  , over(default)  ///
bar(1,color(gs2)) ylabel(,nogrid noticks) plotregion(lcolor(black)) graphregion(fcolor(white) color(white) lcolor(white)) ytitle("Total Asset (Billion)") b1title("Percentage of CRE Default (%)")
graph export "${output}figure_5D.eps", as(eos) replace 



//////////////////////////////////////////////////////////////////////////////
/////////////// Figures 2, 4///////////////////////////////
//////////////////////////////////////////////////////////////////////////////
use "${processed}analysis_loss_2022q1_2024q1",clear
gen cre_loan = reloan-reloan_residential1to4 
drop mm_asset 
forvalue x = 10(1)10{
gen cre_loss = 0.3*cre_loan*`x'/100 // 70% recovery rate 
// with only cre default or extreme case 
gen eta = 100*total_equity/total_asset 
gen mm_eta = 100*(total_equity-loss)/(total_asset-loss) 
gen mm_dd_eta = 100*(total_equity-cre_loss-loss)/(total_asset-loss-cre_loss) 

// with 50% uninsured depositor run 
gen insured_coverage_50 = 100*(total_asset-0.5*uninsured_deposit-insured_deposit)/insured_deposit
gen mm_insured_coverage_50 = 100*(total_asset-loss-0.5*uninsured_deposit-insured_deposit)/insured_deposit
gen mm_dd_insured_coverage_50 = 100*(total_asset-cre_loss-loss-0.5*uninsured_deposit-insured_deposit)/insured_deposit 

// with 100% uninsured depositor run 
gen insured_coverage_100 = 100*(total_asset-uninsured_deposit-insured_deposit)/insured_deposit
gen mm_insured_coverage_100 = 100*(total_asset-loss-uninsured_deposit-insured_deposit)/insured_deposit
gen mm_dd_insured_coverage_100 = 100*(total_asset-cre_loss-loss-uninsured_deposit-insured_deposit)/insured_deposit 

}

sum eta ,detail
local full = round(r(p50),0.1)
sum mm_eta ,detail
local mm = round(r(p50),0.1)
sum mm_dd_eta ,detail
local mm_dd = round(r(p50),0.1)


_pctile eta, nq(100)
replace eta = . if eta>r(r95) & !missing(eta)
replace eta = . if eta<r(r1)
_pctile mm_eta, nq(100)
replace mm_eta = . if mm_eta>r(r95) & !missing(mm_eta)
replace mm_eta = . if mm_eta<r(r1)
_pctile mm_dd_eta, nq(100)
replace mm_dd_eta = . if mm_dd_eta>r(r95) & !missing(mm_dd_eta)
replace mm_dd_eta = . if mm_dd_eta<r(r1)

// Figure 2A

twoway  (histogram eta , density fcolor(gs2%70) fintensity(100) lcolor(gs2%0) gap(0)) ///
(histogram mm_eta , density fcolor(maroon%70) fintensity(100) lcolor(maroon%15) gap(0)) ///
 (histogram mm_dd_eta , density fcolor(lavender%70) fintensity(100) lcolor(lavender%15) gap(0)), ///
 xtitle("Equity/Asset (%)") ytitle("Density") graphregion(fcolor(white) color(white) lcolor(white)) xlabel(,noticks) ylabel(,noticks nogrid) xlabel(, noticks) plotregion(lcolor(black)) legend(on pos(11) ring(0) col(1) order(1 "2022Q1, median=`full'" 2 "w/ rate increase, median=`mm'" 3 "w/ rate increase & 10% CRE default, median=`mm_dd'") size(small) region(color(white) style(none))) 
graph export "${output}figure_2A.eps", as(eps) replace

// Figure 2B 
drop size
gen size = ln(total_asset*1000)

gen bin = 1 if size<=18
forvalue x = 1(1)30{
replace bin = `x'+1 if size>(18+(`x'-1)*0.25) & size<=(18+`x'*0.25)
}
replace bin = 27 if bin>27 // gsib bank 
tab bin, gen(bin)

drop bin 
eststo clear 
eststo full: reg eta bin*  , nocons 
eststo mm: reg mm_eta bin*  , nocons 
eststo mm_dd: reg mm_dd_eta bin*  , nocons 

// loss against size 
coefplot (full,  recast(connected) lpattern(solid) ciopts(recast(rarea) col(gs2%40) lcolor(red%0))  levels(95)  lc(black) msiz(small)  mc(dknavy) msymbol(none) ) ///
(mm,  recast(connected) lpattern(dash) ciopts(recast(rarea) col(red%40) lcolor(red%0))  levels(95) lc(red) msiz(small)  mc(dknavy) msymbol(none) lwidth(thick)) ///
(mm_dd,  recast(connected) lpattern(dash) ciopts(recast(rarea) col(lavender%40) lcolor(red%0))  levels(95) lc(lavender) msiz(small)  mc(dknavy) msymbol(none) lwidth(thick)) ///
, vertical ytitle("Equity/Asset (%)") xtitle("Ln(Asset)") ylabel(0(5)20,nogrid noticks) plotregion(lcolor(black)) graphregion(fcolor(white) color(white) lcolor(white)) xlabel(1 "18" 9 "20" 17 "22" 25 "24"  ,noticks)   legend(on pos(12) ring(0) col(1) order(1 "2022Q1" 3 "w/ rate increase" 5 "w/ rate increase & 10% CRE default") size(small) region(color(white) style(none))) 
graph export "${output}figure_2B.eps", as(eps) replace //2022Q1

///// Figure 4: 50% run 

sum insured_coverage_50 ,detail
local full = round(r(p50),0.1)
sum mm_insured_coverage_50 ,detail
local mm = round(r(p50),0.1)
sum mm_dd_insured_coverage_50 ,detail
local mm_dd = round(r(p50),0.1)


_pctile insured_coverage_50, nq(100)
replace insured_coverage_50 = . if insured_coverage_50>r(r95) & !missing(insured_coverage_50)
_pctile mm_insured_coverage_50, nq(100)
replace mm_insured_coverage_50 = . if mm_insured_coverage_50>r(r95) & !missing(mm_insured_coverage_50)
_pctile mm_dd_insured_coverage_50, nq(100)
replace mm_dd_insured_coverage_50 = . if mm_dd_insured_coverage_50>r(r95) & !missing(mm_dd_insured_coverage_50)


// figure 4A
twoway  (histogram insured_coverage_50 , density fcolor(gs2%70) fintensity(100) lcolor(gs2%0) gap(0)) ///
(histogram mm_insured_coverage_50 , density fcolor(maroon%70) fintensity(100) lcolor(maroon%15) gap(0)) ///
 (histogram mm_dd_insured_coverage_50 , density fcolor(lavender%70) fintensity(100) lcolor(lavender%15) gap(0)), ///
 xtitle("Insured Deposit Coverage Ratio (%)") ytitle("Density") graphregion(fcolor(white) color(white) lcolor(white)) xlabel(,noticks) ylabel(0(0.01)0.04,noticks nogrid) xlabel(, noticks) plotregion(lcolor(black)) legend(on pos(11) ring(0) col(1) order(1 "2022Q1, median=`full'" 2 "w/ rate increase, median=`mm'" 3 "w/ rate increase & 10% CRE default, median=`mm_dd'") size(small) region(color(white) style(none))) 
graph export "${output}figure_4A.eps", as(eps) replace

// figure 4B 
drop size
gen size = ln(total_asset*1000)

eststo clear 
eststo full: reg insured_coverage_50 bin*  , nocons 
eststo mm: reg mm_insured_coverage_50 bin*  , nocons 
eststo mm_dd: reg mm_dd_insured_coverage_50 bin*  , nocons 

// loss against size 
coefplot (full,  recast(connected) lpattern(solid) ciopts(recast(rarea) col(gs2%40) lcolor(red%0))  levels(95)  lc(black) msiz(small)  mc(dknavy) msymbol(none) ) ///
(mm,  recast(connected) lpattern(dash) ciopts(recast(rarea) col(red%40) lcolor(red%0))  levels(95) lc(red) msiz(small)  mc(dknavy) msymbol(none) lwidth(thick)) ///
(mm_dd,  recast(connected) lpattern(dash) ciopts(recast(rarea) col(lavender%40) lcolor(red%0))  levels(95) lc(lavender) msiz(small)  mc(dknavy) msymbol(none) lwidth(thick)) ///
, vertical ytitle("Insured Deposit Coverage Ratio (%)") xtitle("Ln(Asset)") ylabel(,nogrid noticks) plotregion(lcolor(black)) graphregion(fcolor(white) color(white) lcolor(white)) xlabel(1 "18" 9 "20" 17 "22" 25 "24"  ,noticks)   legend(on pos(5) ring(0) col(1) order(1 "2022Q1" 3 "w/ rate increase" 5 "w/ rate increase & 10% CRE default") size(small) region(color(white) style(none))) 
graph export "${output}figure_4B.eps", as(eps) replace //2022Q1

////Figure 4: 100% run 

sum insured_coverage_100 ,detail
local full = round(r(p50),0.1)
sum mm_insured_coverage_100 ,detail
local mm = round(r(p50),0.1)
sum mm_dd_insured_coverage_100 ,detail
local mm_dd = round(r(p50),0.1)


_pctile insured_coverage_100, nq(100)
replace insured_coverage_100 = . if insured_coverage_100>r(r95) & !missing(insured_coverage_100)
replace insured_coverage_100 = . if insured_coverage_100<r(r1)
_pctile mm_insured_coverage_100, nq(100)
replace mm_insured_coverage_100 = . if mm_insured_coverage_100>r(r95) & !missing(mm_insured_coverage_100)
replace mm_insured_coverage_100 = . if mm_insured_coverage_100<r(r1)
_pctile mm_dd_insured_coverage_100, nq(100)
replace mm_dd_insured_coverage_100 = . if mm_dd_insured_coverage_100>r(r95) & !missing(mm_dd_insured_coverage_100)
replace mm_dd_insured_coverage_100 = . if mm_dd_insured_coverage_100<r(r1)

// Figure 4C
twoway  (histogram insured_coverage_100 , density fcolor(gs2%70) fintensity(100) lcolor(gs2%0) gap(0)) ///
(histogram mm_insured_coverage_100 , density fcolor(maroon%70) fintensity(100) lcolor(maroon%15) gap(0)) ///
 (histogram mm_dd_insured_coverage_100 , density fcolor(lavender%70) fintensity(100) lcolor(lavender%15) gap(0)), ///
 xtitle("Insured Deposit Coverage Ratio (%)") ytitle("Density") graphregion(fcolor(white) color(white) lcolor(white)) xlabel(,noticks) ylabel(,noticks nogrid) xlabel(, noticks) plotregion(lcolor(black)) legend(on pos(11) ring(0) col(1) order(1 "2022Q1, median=`full'" 2 "w/ rate increase, median=`mm'" 3 "w/ rate increase & 10% CRE default, median=`mm_dd'") size(small) region(color(white) style(none))) 
graph export "${output}figure_4C.eps", as(eps) replace

// Figure 4D 

eststo clear 
eststo full: reg insured_coverage_100 bin*  , nocons 
eststo mm: reg mm_insured_coverage_100 bin*  , nocons 
eststo mm_dd: reg mm_dd_insured_coverage_100 bin*  , nocons 

// loss against size 
coefplot (full,  recast(connected) lpattern(solid) ciopts(recast(rarea) col(gs2%40) lcolor(red%0))  levels(95)  lc(black) msiz(small)  mc(dknavy) msymbol(none) ) ///
(mm,  recast(connected) lpattern(dash) ciopts(recast(rarea) col(red%40) lcolor(red%0))  levels(95) lc(red) msiz(small)  mc(dknavy) msymbol(none) lwidth(thick)) ///
(mm_dd,  recast(connected) lpattern(dash) ciopts(recast(rarea) col(lavender%40) lcolor(red%0))  levels(95) lc(lavender) msiz(small)  mc(dknavy) msymbol(none) lwidth(thick)) ///
, vertical ytitle("Insured Deposit Coverage Ratio (%)") xtitle("Ln(Asset)") ylabel(,nogrid noticks) plotregion(lcolor(black)) graphregion(fcolor(white) color(white) lcolor(white)) xlabel(1 "18" 9 "20" 17 "22" 25 "24"  ,noticks)   legend(on pos(11) ring(0) col(1) order(1 "2022Q1" 3 "w/ rate increase" 5 "w/ rate increase & 10% CRE default") size(small) region(color(white) style(none))) 
graph export "${output}figure_4D.eps", as(eps) replace //2022Q1


//////////////////////////////////////////////////////////////////////////////
//////////// Figure 6: Capital Ratio////////////////////////////
//////////////////////////////////////////////////////////////////////////////
use "${processed}analysis_loss_2022q1_2024q1",clear
gen cre_loan = reloan-reloan_residential1to4 
drop mm_asset 
forvalue x = 10(1)10{
gen cre_loss = 0.3*cre_loan*`x'/100 // 70% recovery rate 
// with 50% uninsured depositor run (+10% CRE default) 
gen capital_ratio_50 = 100*(total_equity)/total_asset 
gen mm_capital_ratio_50 =100*(total_equity-0.5*uninsured_deposit*(total_asset/(total_asset-loss)-1))/total_asset 
gen mm_dd_capital_ratio_50 =100*(total_equity-cre_loss-0.5*uninsured_deposit*(total_asset/(total_asset-loss)-1))/(total_asset)

// with 100% uninsured depositor run (+10% CRE default) 
gen capital_ratio_100 = 100*(total_equity)/total_asset 
gen mm_capital_ratio_100 =100*(total_equity-uninsured_deposit*(total_asset/(total_asset-loss)-1))/total_asset 
gen mm_dd_capital_ratio_100 =100*(total_equity-cre_loss-uninsured_deposit*(total_asset/(total_asset-loss)-1))/(total_asset)

}

sum capital_ratio_50 ,detail
local full = round(r(p50),0.1)
sum mm_capital_ratio_50 ,detail
local mm = round(r(p50),0.1)
sum mm_dd_capital_ratio_50 ,detail
local mm_dd = round(r(p50),0.1)


_pctile capital_ratio_50, nq(100)
replace capital_ratio_50 = . if capital_ratio_50>r(r95) & !missing(capital_ratio_50)
_pctile mm_capital_ratio_50, nq(100)
replace mm_capital_ratio_50 = . if mm_capital_ratio_50>r(r95) & !missing(mm_capital_ratio_50)
_pctile mm_dd_capital_ratio_50, nq(100)
replace mm_dd_capital_ratio_50 = . if mm_dd_capital_ratio_50>r(r95) & !missing(mm_dd_capital_ratio_50)

// Figure 6A
twoway  (histogram capital_ratio_50 , density fcolor(gs2%70) fintensity(100) lcolor(gs2%0) gap(0)) ///
(histogram mm_capital_ratio_50 , density fcolor(maroon%70) fintensity(100) lcolor(maroon%15) gap(0)) ///
 (histogram mm_dd_capital_ratio_50 , density fcolor(lavender%70) fintensity(100) lcolor(lavender%15) gap(0)), ///
 xtitle("Capital Ratio (%)") ytitle("Density") graphregion(fcolor(white) color(white) lcolor(white)) xlabel(-20(10)20,noticks) ylabel(,noticks nogrid) xlabel(, noticks) plotregion(lcolor(black)) legend(on pos(11) ring(0) col(1) order(1 "2022Q1, median=`full'" 2 "w/ rate increase, median=`mm'" 3 "w/ rate increase & 10% CRE default, median=`mm_dd'") size(small) region(color(white) style(none))) 
graph export "${output}figure_6A.eps", as(eps) replace

// Figure 6B 
drop size
gen size = ln(total_asset*1000)

gen bin = 1 if size<=18
forvalue x = 1(1)30{
replace bin = `x'+1 if size>(18+(`x'-1)*0.25) & size<=(18+`x'*0.25)
}
replace bin = 27 if bin>27 // gsib bank 
tab bin, gen(bin)
drop bin

eststo clear 
eststo full: reg capital_ratio_50 bin*  , nocons 
eststo mm: reg mm_capital_ratio_50 bin*  , nocons 
eststo mm_dd: reg mm_dd_capital_ratio_50 bin*  , nocons 

// loss against size 
coefplot (full,  recast(connected) lpattern(solid) ciopts(recast(rarea) col(gs2%40) lcolor(red%0))  levels(95)  lc(black) msiz(small)  mc(dknavy) msymbol(none) ) ///
(mm,  recast(connected) lpattern(dash) ciopts(recast(rarea) col(red%40) lcolor(red%0))  levels(95) lc(red) msiz(small)  mc(dknavy) msymbol(none) lwidth(thick)) ///
(mm_dd,  recast(connected) lpattern(dash) ciopts(recast(rarea) col(lavender%40) lcolor(red%0))  levels(95) lc(lavender) msiz(small)  mc(dknavy) msymbol(none) lwidth(thick)) ///
, vertical ytitle("Capital Ratio (%)") xtitle("Ln(Asset)") ylabel(0(5)10,nogrid noticks) plotregion(lcolor(black)) graphregion(fcolor(white) color(white) lcolor(white)) xlabel(1 "18" 9 "20" 17 "22" 25 "24"  ,noticks)   legend(on pos(5) ring(0) col(1) order(1 "2022Q1" 3 "w/ rate increase" 5 "w/ rate increase & 10% CRE default") size(small) region(color(white) style(none))) 
graph export "${output}figure_6B.eps", as(eps) replace //2022Q1

//////// 100% run 

sum capital_ratio_100 ,detail
local full = round(r(p50),0.1)
sum mm_capital_ratio_100 ,detail
local mm = round(r(p50),1)
sum mm_dd_capital_ratio_100 ,detail
local mm_dd = round(r(p50),0.1)



_pctile capital_ratio_100, nq(100)
replace capital_ratio_100 = . if capital_ratio_100>r(r95) & !missing(capital_ratio_100)
_pctile mm_capital_ratio_100, nq(100)
replace mm_capital_ratio_100 = . if mm_capital_ratio_100>r(r95) & !missing(mm_dd_capital_ratio_100)
_pctile mm_dd_capital_ratio_100, nq(100)
replace mm_dd_capital_ratio_100 = . if mm_dd_capital_ratio_100>r(r95) & !missing(mm_dd_capital_ratio_100)

// figure 6C
twoway  (histogram capital_ratio_100 , density fcolor(gs2%70) fintensity(100) lcolor(gs2%0) gap(0)) ///
(histogram mm_capital_ratio_100 , density fcolor(maroon%70) fintensity(100) lcolor(maroon%15) gap(0)) ///
 (histogram mm_dd_capital_ratio_100 , density fcolor(lavender%70) fintensity(100) lcolor(lavender%15) gap(0)), ///
 xtitle("Capital Ratio (%)") ytitle("Density") graphregion(fcolor(white) color(white) lcolor(white)) xlabel(,noticks) ylabel(,noticks nogrid) xlabel(, noticks) plotregion(lcolor(black)) legend(on pos(11) ring(0) col(1) order(1 "2022Q1, median=`full'" 2 "w/ rate increase, median=`mm'" 3 "w/ rate increase & 10% CRE default, median=`mm_dd'") size(small) region(color(white) style(none))) 
graph export "${output}figure_6C.eps", as(eps) replace

// Figure 6D

eststo clear 
eststo full: reg capital_ratio_100 bin*  , nocons 
eststo mm: reg mm_capital_ratio_100 bin*  , nocons 
eststo mm_dd: reg mm_dd_capital_ratio_100 bin*  , nocons 

// loss against size 
coefplot (full,  recast(connected) lpattern(solid) ciopts(recast(rarea) col(gs2%40) lcolor(red%0))  levels(95)  lc(black) msiz(small)  mc(dknavy) msymbol(none) ) ///
(mm,  recast(connected) lpattern(dash) ciopts(recast(rarea) col(red%40) lcolor(red%0))  levels(95) lc(red) msiz(small)  mc(dknavy) msymbol(none) lwidth(thick)) ///
(mm_dd,  recast(connected) lpattern(dash) ciopts(recast(rarea) col(lavender%40) lcolor(red%0))  levels(95) lc(lavender) msiz(small)  mc(dknavy) msymbol(none) lwidth(thick)) ///
, vertical ytitle("Capital Ratio (%)") xtitle("Ln(Asset)") ylabel(,nogrid noticks) plotregion(lcolor(black)) graphregion(fcolor(white) color(white) lcolor(white)) xlabel(1 "18" 9 "20" 17 "22" 25 "24"  ,noticks)   legend(on pos(11) ring(0) col(1) order(1 "2022Q1" 3 "w/ rate increase" 5 "w/ rate increase & 10% CRE default") size(small) region(color(white) style(none))) 
graph export "${output}figure_6D.eps", as(eps) replace //2022Q1



//////////////////////////////////////////////////////////////////////////////
//////////// Figure 7: Capital Ratio////////////////////////////
//////////////////////////////////////////////////////////////////////////////

use "${processed}analysis_loss_2022q1_2024q1",clear

gen cre_loan = reloan-reloan_residential1to4 

forvalue x = 0(2)20{
gen cre_loss = 0.3*cre_loan*`x'/100 // 70% recovery rate 


// with 50% insured depositor run 
gen mm_dd_capital_ratio=100*(total_equity-cre_loss-0.5*uninsured_deposit*(total_asset/(total_asset-loss)-1))/(total_asset)
gen neg_mm_capital = (mm_dd_capital_ratio<0)
gen mm_dd_equity = total_equity-cre_loss-0.5*uninsured_deposit*(total_asset/(total_asset-loss)-1)

egen count_neg_50run_mm_`x' = sum(neg_mm_capital)
egen agg_asset_50run_neg_mm_`x' = sum(neg_mm_capital*total_asset)
egen agg_equity_50run_deficit_mm_`x' = sum(neg_mm_capital*mm_dd_equity)

drop   mm_dd_capital_ratio neg_mm_capital mm_dd_equity 

// with 100% insured depositor run 
gen mm_dd_capital_ratio=100*(total_equity-cre_loss-1*uninsured_deposit*(total_asset/(total_asset-loss)-1))/(total_asset)

gen neg_mm_capital = (mm_dd_capital_ratio<0)
gen mm_dd_equity = total_equity-cre_loss-1*uninsured_deposit*(total_asset/(total_asset-loss)-1)

egen count_neg_100run_mm_`x' = sum(neg_mm_capital)
egen agg_asset_100run_neg_mm_`x' = sum(neg_mm_capital*total_asset)
egen agg_equity_100run_deficit_mm_`x' = sum(neg_mm_capital*mm_dd_equity)

drop   mm_dd_capital_ratio neg_mm_capital mm_dd_equity 


drop cre_loss

}



keep  count_neg_* agg_asset_* agg_equity_* 
duplicates drop

gen id = 1 
reshape long    count_neg_50run_mm_  agg_asset_50run_neg_mm_ agg_equity_50run_deficit_mm_   count_neg_100run_mm_  agg_asset_100run_neg_mm_  agg_equity_100run_deficit_mm_, i(id) j(default)

foreach x of var  agg_equity*  {
replace `x' = `x'/1000000 // billion
}

foreach x of var agg_asset_* {
replace `x' = `x'/1000000 // billion
}

// 50% run 

sort id default 
by id:gen extra_n_neg_50 = count_neg_50run_mm_-count_neg_50run_mm_[1]
by id: gen extra_asset_neg_50 = (agg_asset_50run_neg_mm_-agg_asset_50run_neg_mm_[1])


graph bar extra_n_neg_50 if default>0  , over(default)  ///
bar(1,color(gs2)) ylabel(,nogrid noticks) plotregion(lcolor(black)) graphregion(fcolor(white) color(white) lcolor(white)) ytitle("Number of Banks") b1title("Percentage of CRE Default (%)")
graph export "${output}figure_7A.eps", as(eps) replace 

graph bar extra_asset_neg_50 if default>0  , over(default)  ///
bar(1,color(gs2)) ylabel(,nogrid noticks) plotregion(lcolor(black)) graphregion(fcolor(white) color(white) lcolor(white)) ytitle("Total Asset (Billion)") b1title("Percentage of CRE Default (%)")
graph export "${output}figure_7B.eps", as(eps) replace 

// 100% run 
sort id default 
by id:gen extra_n_neg_100 = count_neg_100run_mm_-count_neg_100run_mm_[1]
by id: gen extra_asset_neg_100 = (agg_asset_100run_neg_mm_-agg_asset_100run_neg_mm_[1])/1000 // trillion


graph bar extra_n_neg_100 if default>0  , over(default)  ///
bar(1,color(gs2)) ylabel(,nogrid noticks) plotregion(lcolor(black)) graphregion(fcolor(white) color(white) lcolor(white)) ytitle("Number of Banks") b1title("Percentage of CRE Default (%)")
graph export "${output}figure_7C.eps", as(eps) replace 

graph bar extra_asset_neg_100 if default>0  , over(default)  ///
bar(1,color(gs2)) ylabel(,nogrid noticks) plotregion(lcolor(black)) graphregion(fcolor(white) color(white) lcolor(white)) ytitle("Total Asset (Trillion)") b1title("Percentage of CRE Default (%)")
graph export "${output}figure_7D.eps", as(eps) replace 


//////////////////////////////////////////////////////////////////////////////
//////////// Figures 8 - 10////////////////////////////
//////////////////////////////////////////////////////////////////////////////
/// Figures 8-10 are made in excel
/// excel files can be found in the following excel files:
// Figure 8a: Replication_Package\Data\processed\Figure 8a.xls
// Figure 8b: Replication_Package\Data\processed\Figure 8b.xls
// Figure 9a: Replication_Package\Data\processed\Figure 9a.xls
// Figure 9b: Replication_Package\Data\processed\Figure 9b.xls
// Figure 10a: Replication_Package\Data\processed\Figure 10a.xls
// Figure 10b: Replication_Package\Data\processed\Figure 10b.xls

//////////////////////////////////////////////////////////////////////////////
//////////// Figures 11 & 12: Non-Performing CRE Loans////////////////////////////
//////////////////////////////////////////////////////////////////////////////
/////////////////////////// bank-level analysis ////////////////////////////////
use "${processed}analysis_cre_dynamic_state",clear 

gen ln_cre = ln(cre_loan)

// DiD coefficient plot  
tab date, gen(date) // date = 251 is 2022Q4 

gen low_capital_100 = (bin_capital_ratio_100<=2)
gen low_udrr_100 = (bin_insured_coverage_mm_100<=2)

gen low_capital_50 = (bin_capital_ratio_50<=2)
gen low_udrr_50 = (bin_insured_coverage_mm_50<=2)

gen state_charter = (charter=="STATE")

gen nmb = 1 if regagnt == "FDIC"
replace nmb = 0 if charter=="FED"
gen smb = 1 if regagnt == "FED"
replace smb = 0 if charter=="FED"

foreach x of var date1-date14{
gen `x'_state = `x'*state_charter
gen `x'_nmb = `x'*nmb 
gen `x'_smb = `x'*smb
}
replace date4_state = 0 
replace date4_nmb = 0 
replace date4_smb = 0 


///Dynamic DiD plot
eststo clear
eststo state_100_low_cap: reghdfe s_nonperform_cre date*_state  i.date##c.ave_ln_asset  if low_capital_100==1, a(idrssd date) vce(cluster idrssd)
eststo state_100_high_cap: reghdfe s_nonperform_cre date*_state  i.date##c.ave_ln_asset  if low_capital_100==0, a(idrssd date) vce(cluster idrssd)

eststo nmb_100_low_cap: reghdfe s_nonperform_cre date*_nmb  i.date##c.ave_ln_asset  if low_capital_100==1, a(idrssd date) vce(cluster idrssd)
eststo smb_100_low_cap: reghdfe s_nonperform_cre date*_smb  i.date##c.ave_ln_asset  if low_capital_100==1, a(idrssd date) vce(cluster idrssd)

eststo nmb_100_high_cap: reghdfe s_nonperform_cre date*_nmb  i.date##c.ave_ln_asset  if low_capital_100==0, a(idrssd date) vce(cluster idrssd)
eststo smb_100_high_cap: reghdfe s_nonperform_cre date*_smb  i.date##c.ave_ln_asset  if low_capital_100==0, a(idrssd date) vce(cluster idrssd)


//// state- vs fed-chartered 
coefplot state_100_low_cap,  title("Non-Performing CRE Loan Share (pp): State- vs Fed-Chartered", size(medium) color(black)) subtitle("(Below-Median MTM Capital Ratio Banks)", size(medsmall) color(black)) ///
recast(connected) ciopts(recast(rcap)) color(black) vertical omitted  keep(date*_state)  xlabel(1 "2021Q1" 3 "Q3"  5 "2022Q1"  7 "Q3"  9 "2023Q1"  11 "Q3" 13 "2024Q1")  xtitle("") graphregion(fcolor(white) color(white) lcolor(white)) ylabel(-1(0.5)0.5, nogrid glcolor(gs8) gstyle(dot)) xline(5,lpattern(dash) lcolor(maroon)) xline(9,lpattern(dash) lcolor(maroon)) plotregion(lcolor(black)) yline(0, lpattern(solid) lcolor(maroon))
graph export "${output}figure_11A.eps", as(eps) replace 



coefplot state_100_high_cap, title("Non-Performing CRE Loan Share (pp): State- vs Fed-Chartered", size(medium) color(black)) subtitle("(Above-Median MTM Capital Ratio Banks)", size(medsmall) color(black)) ///
recast(connected) ciopts(recast(rcap)) color(black) vertical omitted  keep(date*_state)  xlabel(1 "2021Q1" 3 "Q3"  5 "2022Q1"  7 "Q3"  9 "2023Q1"  11 "Q3" 13 "2024Q1")   xtitle("") graphregion(fcolor(white) color(white) lcolor(white)) ylabel(-1(0.5)0.5, nogrid glcolor(gs8) gstyle(dot)) xline(5,lpattern(dash) lcolor(maroon)) xline(9,lpattern(dash) lcolor(maroon)) plotregion(lcolor(black)) yline(0, lpattern(solid) lcolor(maroon))
graph export "${output}figure_11B.eps", as(eps) replace 


////////////// figure 12 ////////
//// NMB and SMB vs Fed-chartered 

coefplot nmb_100_low_cap ,  title("Non-Performing CRE Loan Share (pp): State Non-Member- vs Fed-Chartered", size(medium) color(black)) subtitle("(Below-Median MTM Capital Ratio Banks)", size(medsmall) color(black)) ///
recast(connected) ciopts(recast(rcap)) color(black) vertical omitted  keep(date*_nmb date*_smb)  xlabel(1 "2021Q1" 3 "Q3"  5 "2022Q1"  7 "Q3"  9 "2023Q1"  11 "Q3" 13 "2024Q1")  xtitle("") graphregion(fcolor(white) color(white) lcolor(white)) ylabel(-1(0.5)0.5, nogrid glcolor(gs8) gstyle(dot)) xline(5,lpattern(dash) lcolor(maroon)) xline(9,lpattern(dash) lcolor(maroon)) plotregion(lcolor(black)) yline(0, lpattern(solid) lcolor(maroon))
graph export "${output}figure_12A.eps", as(eps) replace 


coefplot smb_100_low_cap ,  title("Non-Performing CRE Loan Share (pp): State Member- vs Fed-Chartered", size(medium) color(black)) subtitle("(Below-Median MTM Capital Ratio Banks)", size(medsmall) color(black)) ///
recast(connected) ciopts(recast(rcap)) color(black) vertical omitted  keep(date*_nmb date*_smb)  xlabel(1 "2021Q1" 3 "Q3"  5 "2022Q1"  7 "Q3"  9 "2023Q1"  11 "Q3" 13 "2024Q1")  xtitle("") graphregion(fcolor(white) color(white) lcolor(white)) ylabel(-1(0.5)0.5, nogrid glcolor(gs8) gstyle(dot)) xline(5,lpattern(dash) lcolor(maroon)) xline(9,lpattern(dash) lcolor(maroon)) plotregion(lcolor(black)) yline(0, lpattern(solid) lcolor(maroon))
graph export "${output}figure_12B.eps", as(eps) replace 


coefplot nmb_100_high_cap ,  title("Non-Performing CRE Loan Share (pp): State Non-Member- vs Fed-Chartered", size(medium) color(black)) subtitle("(Above-Median MTM Capital Ratio Banks)", size(medsmall) color(black)) ///
recast(connected) ciopts(recast(rcap)) color(black) vertical omitted  keep(date*_nmb date*_smb)  xlabel(1 "2021Q1" 3 "Q3"  5 "2022Q1"  7 "Q3"  9 "2023Q1"  11 "Q3" 13 "2024Q1")  xtitle("") graphregion(fcolor(white) color(white) lcolor(white)) ylabel(-1(0.5)0.5, nogrid glcolor(gs8) gstyle(dot)) xline(5,lpattern(dash) lcolor(maroon)) xline(9,lpattern(dash) lcolor(maroon)) plotregion(lcolor(black)) yline(0, lpattern(solid) lcolor(maroon))
graph export "${output}figure_12C.eps", as(eps) replace 


coefplot smb_100_high_cap ,  title("Non-Performing CRE Loan Share (pp): State Member- vs Fed-Chartered", size(medium) color(black)) subtitle("(Above-Median MTM Capital Ratio Banks)", size(medsmall) color(black)) ///
recast(connected) ciopts(recast(rcap)) color(black) vertical omitted  keep(date*_nmb date*_smb)  xlabel(1 "2021Q1" 3 "Q3"  5 "2022Q1"  7 "Q3"  9 "2023Q1"  11 "Q3" 13 "2024Q1")  xtitle("") graphregion(fcolor(white) color(white) lcolor(white)) ylabel(-1(0.5)0.5, nogrid glcolor(gs8) gstyle(dot)) xline(5,lpattern(dash) lcolor(maroon)) xline(9,lpattern(dash) lcolor(maroon)) plotregion(lcolor(black)) yline(0, lpattern(solid) lcolor(maroon))
graph export "${output}figure_12D.eps", as(eps) replace 

